
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mfi.models;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import javax.servlet.http.HttpSession;

/**
 *
 * @author administrator2
 */
public class DBConnection {

    private Connection connection;
    private String url;
    private String user;
    private String pass;

    public DBConnection() {
        this.connection = null;
        //this.url = "jdbc:mysql://localhost:3306/hotbite?noAccessToProcedureBodies=true";
        this.url = "jdbc:mysql://127.0.0.1:3306/hotbite?noAccessToProcedureBodies=true";
        this.user = "root";
        this.pass = "password";
    }

    public boolean connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(this.url, this.user, this.pass);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    public boolean insert(String email, String password, String firstname, String lastname, String gender, String birthdate, String contact, String addTitle, String address, String landmark) {
        try {
            this.connect();

            CallableStatement statement = connection.prepareCall("{CALL sp_accounts_insert(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");

            statement.setString(1, email);
            statement.setString(2, password);
            statement.setString(3, firstname);
            statement.setString(4, lastname);
            statement.setString(5, gender);
            statement.setString(6, birthdate);
            statement.setString(7, address);
            statement.setString(8, addTitle);
            statement.setString(9, landmark);
            statement.setString(10, contact);

            ResultSet result = statement.executeQuery();

            return (result.next() && (result.getInt("total") != 0)) ? true : false;

        } catch (Exception e) {
            return false;
        }
    }

    public boolean emailCheck(String email) {
        try {

            if (this.connection == null) {
                this.connect();
            }

            CallableStatement statement = this.connection.prepareCall("{CALL sp_accounts_email_check(?)}");

            statement.setString(1, email);


            ResultSet result = statement.executeQuery();

            return result.next() && result.getInt("total") == 0 ? true : false;
            //return "test";

        } catch (Exception e) {
            return false;
            //return e.toString();
        }
    }

    public boolean updateCheck(String email, int id) {
        try {

            if (this.connection == null) {
                this.connect();
            }

            CallableStatement statement = this.connection.prepareCall("{CALL sp_accounts_update_email(?, ?)}");

            statement.setString(1, email);
            statement.setInt(2, id);


            ResultSet result = statement.executeQuery();

            return result.next() && result.getInt("total") == 0 ? true : false;
            //return "test";

        } catch (Exception e) {
            return false;
            //return e.toString();
        }
    }

    public Customer validateLogin(String email, String password) {
        try {

            this.connect();
            Customer c = null;

            CallableStatement statement = connection.prepareCall("{CALL sp_accounts_login(?,?)}");
            
            statement.setString(1, email);
            statement.setString(2, password);

            ResultSet result = statement.executeQuery();

            if (result.next()) {

                int id;
                String firstName;
                String lastName;
                String gender;
                String birthDate;
                //String address; --DEPRECATED
                String contact;
                String type;
                boolean active;

                id = result.getInt("id");
                firstName = result.getString("first_name");
                lastName = result.getString("last_name");
                gender = result.getString("gender");
                birthDate = result.getString("birth_date");
                //address = result.getString("address"); --DEPRECATED
                contact = result.getString("contact");
                type = result.getString("type");
                active = result.getBoolean("active");


                //c = new Customer(id, email, password, firstName, lastName, gender, birthDate, address, contact, type, active);
                c = new Customer(id, email, password, firstName, lastName, gender, birthDate, contact, type, active);
                
            }

            return c;

        } catch (Exception e) {
            return null;
        }
    }

    public ArrayList getCategories() {

        try {
            this.connect();

            ArrayList<Category> categories = new ArrayList<Category>();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_categories_select_all() }");
            ResultSet result = statement.executeQuery();

            Category tempCategory = null;

            while (result.next()) {
                tempCategory = new Category(result.getInt("category_id"), result.getString("name"), result.getBoolean("active"));
                categories.add(tempCategory);
            }

            return categories;

        } catch (Exception ex) {
            return null;
        }

    }
    
    public ArrayList getActiveCategories() {

        try {
            this.connect();

            ArrayList<Category> categories = new ArrayList<Category>();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_categories_select_active() }");
            ResultSet result = statement.executeQuery();

            Category tempCategory = null;

            while (result.next()) {
                tempCategory = new Category(result.getInt("category_id"), result.getString("name"), result.getBoolean("active"));
                categories.add(tempCategory);
            }

            return categories;

        } catch (Exception ex) {
            return null;
        }

    }

    public boolean deleteCategory(String id) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_categories_delete(?) }");
            statement.setString(1, id);
            ResultSet result = statement.executeQuery();

            return result.next() && result.getInt("total") > 0 ? true : false;
        } catch (Exception ex) {
            return false;
        }
    }

    public boolean restoreCategory(String id) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_categories_restore(?) }");
            statement.setString(1, id);
            ResultSet result = statement.executeQuery();

            return result.next() && result.getInt("total") > 0 ? true : false;
        } catch (Exception ex) {
            return false;
        }
    }

    public boolean addCategory(String name) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_categories_insert(?) }");
            statement.setString(1, name);
            ResultSet result = statement.executeQuery();

            return (result.next() && (result.getInt("total") != 0)) ? true : false;
        } catch (Exception ex) {
            return false;
        }


    }

    public boolean updateCategory(String id, String name, boolean active) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_categories_update(?,?,?) }");
            statement.setString(1, id);
            statement.setString(2, name);
            statement.setBoolean(3, active);
            ResultSet result = statement.executeQuery();

            return (result.next() && (result.getInt("total") != 0)) ? true : false;
        } catch (Exception ex) {
            return false;
        }


    }

    public Category update(String id) {
        try {
            this.connect();
            CallableStatement statement = this.connection.prepareCall("{ CALL sp_categories_select(?) }");
            statement.setString(1, id);
            ResultSet result = statement.executeQuery();

            Category tempCategory = null;

            while (result.next()) {
                tempCategory = new Category(result.getInt("category_id"), result.getString("name"), result.getBoolean("active"));
            }

            return tempCategory;
        } catch (Exception ex) {
            return null;
        }

    }

    public ArrayList getProducts() {
        try {
            this.connect();
            ArrayList<Product> products = new ArrayList<Product>();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_products_select_all() }");
            ResultSet result = statement.executeQuery();

            Product tempProduct = null;

            while (result.next()) {
                tempProduct = new Product(result.getInt("id"), result.getString("name"), result.getInt("category_id"), result.getString("description"), result.getBoolean("active"), result.getString("image"));
                products.add(tempProduct);
            }

            return products;

        } catch (Exception ex) {
            return null;
        }

    }

    public Product selectProduct(String id) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_products_select(?) }");
            statement.setString(1, id);
            ResultSet result = statement.executeQuery();

            Product tempProduct = null;

            while (result.next()) {
                tempProduct = new Product(result.getInt("id"), result.getString("name"), result.getInt("category_id"), result.getString("description"), result.getBoolean("active"), result.getString("image"));
            }
            return tempProduct;

        } catch (Exception ex) {
            return null;
        }
    }

    public ArrayList getProductsByCategory(int category_id) {

        try {
            ArrayList<Product> products = new ArrayList<Product>();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_products_select_by_category(?) }");
            statement.setInt(1, category_id);
            ResultSet result = statement.executeQuery();

            Product tempProduct = null;

            while (result.next()) {
                tempProduct = new Product(result.getInt("id"), result.getString("name"), result.getInt("category_id"), result.getString("description"), result.getBoolean("active"), result.getString("image"), result.getDouble("price"));
                products.add(tempProduct);
            }

            return products;

        } catch (Exception ex) {
            return null;
        }

    }

    public boolean addProduct(Product p) {
        try {
            this.connect();
            CallableStatement statement = this.connection.prepareCall("{ CALL sp_products_insert(?,?,?,?) }");
            statement.setString(1, p.getName());
            statement.setInt(2, p.getCategory_id());
            statement.setString(3, p.getDescription());
            statement.setString(4, p.getImage());

            ResultSet result = statement.executeQuery();

            return (result.next() && (result.getInt("total") != 0)) ? true : false;

        } catch (Exception ex) {
            return false;
        }
    }

    public boolean modifyProduct(Product p) {
        try {
            this.connect();
            CallableStatement statement = this.connection.prepareCall("{ CALL sp_products_update(?, ?, ?, ?, ?) }");

            statement.setString(1, p.getName());
            statement.setInt(2, p.getCategory_id());
            statement.setString(3, p.getDescription());
            statement.setString(4, p.getImage());
            statement.setInt(5, p.getId());

            ResultSet result = statement.executeQuery();

            return (result.next() && (result.getInt("total") != 0)) ? true : false;
            //return "Success";
        } catch (Exception ex) {
            return false;
            //return ex.getMessage();
        }
    }

    public boolean deleteProduct(String id) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_products_delete(?) }");

            statement.setString(1, id);

            ResultSet result = statement.executeQuery();

            return (result.next() && result.getInt("total") > 0) ? true : false;

        } catch (Exception ex) {
            return false;
        }
    }

    public boolean restoreProduct(String id) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_products_restore(?) }");

            statement.setString(1, id);

            ResultSet result = statement.executeQuery();

            return (result.next() && result.getInt("total") > 0) ? true : false;

        } catch (Exception ex) {
            return false;
        }
    }

    public boolean modifyProfile(String email, String password, String firstname, String lastname, String gender, String birthdate, String contact, int id, Customer c) {
        try {
            this.connect();

            CallableStatement statement = connection.prepareCall("{CALL sp_accounts_update(?, ?, ?, ?, ?, ?, ?, ?)}");

            statement.setString(1, email);
            statement.setString(2, password);
            statement.setString(3, firstname);
            statement.setString(4, lastname);
            statement.setString(5, gender);
            statement.setString(6, birthdate);
            statement.setString(7, contact);
            statement.setInt(8, id);

            if (password != null) {
                c.setPassword(password);
            }

            c.setFirstName(firstname);
            c.setLastName(lastname);
            c.setEmail(email);
            c.setGender(gender);
            c.setBirthDate(birthdate);
            c.setContact(contact);


            ResultSet result = statement.executeQuery();

            return (result.next() && (result.getInt("total") != 0)) ? true : false;

        } catch (Exception e) {
            return false;
        }
    }
    
    public boolean addAddress(String addTitle, String add, String landmark, int id){
        try {
            this.connect();

            CallableStatement statement = connection.prepareCall("{CALL sp_addresses_insert(?, ?, ?, ?)}");

            statement.setInt(1, id);
            statement.setString(2, addTitle);
            statement.setString(3, add);
            statement.setString(4, landmark);

            ResultSet result = statement.executeQuery();

            return (result.next() && (result.getInt("total") != 0)) ? true : false;

        } catch (Exception e) {
            return false;
        }
    }

    public double getPizzaPrice(int id, int size) {
        try {
            this.connect();

            PreparedStatement statement = connection.prepareStatement("SELECT `price` FROM `prices` WHERE `product_id` = ? AND `size_id` = ?");
            statement.setInt(1, id);
            statement.setInt(2, size);
            ResultSet result = statement.executeQuery();

            if (result.next()) {
                return result.getDouble("price");
            }
            return 0;

        } catch (Exception ex) {
            return 0;
        }
    }

    public ArrayList<Size> getProductSizes() {
        try {
            this.connect();

            PreparedStatement statement = connection.prepareStatement("SELECT * FROM `sizes`");
            ResultSet result = null;
            ArrayList<Size> sizes = new ArrayList<Size>();
            result = statement.executeQuery();
            Size s;
            while (result.next()) {
                s = new Size();
                s.setId(result.getInt("id"));
                s.setName(result.getString("name"));
                sizes.add(s);
            }
            
            return sizes;
            
        } catch (Exception ex) {
            return null;
        }
    }

    public int countPizzaSizes() {
        try {
            this.connect();

            PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) AS `total` FROM `sizes`");
            ResultSet result = null;
            result = statement.executeQuery();

            return result.next() ? result.getInt("total") : 0;
        } catch (Exception ex) {
            return 0;
        }
    }

    public ArrayList getCustomers() {
        try {
            this.connect();
            ArrayList<Customer> customer = new ArrayList<Customer>();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_accounts_select_all() }");
            ResultSet result = statement.executeQuery();

            Customer cus = null;

            while (result.next()) {
                cus = new Customer(result.getInt("id"), result.getString("email"), result.getString("password"), result.getString("first_name"), result.getString("last_name"), result.getString("gender"), result.getString("birth_date"), result.getString("contact"), result.getString("type"), result.getBoolean("active"));
                customer.add(cus);
            }

            return customer;


        } catch (Exception ex) {
            return null;
        }

    }

    public boolean deactivateAccount(String id) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_accounts_deactivate(?) }");
            statement.setString(1, id);
            ResultSet result = statement.executeQuery();

            return result.next() && result.getInt("total") > 0 ? true : false;
        } catch (Exception ex) {
            return false;
        }
    }

    public boolean activateAccount(String id) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_accounts_activate(?) }");
            statement.setString(1, id);
            ResultSet result = statement.executeQuery();

            return result.next() && result.getInt("total") > 0 ? true : false;
        } catch (Exception ex) {
            return false;
        }
    }
    
    public ArrayList<Product> getProductsWithPrices() {
        try {
            
            this.connect();
            
            CallableStatement statement = this.connection.prepareCall("{ CALL sp_products_select_all_w_prices() }");
            ResultSet result = statement.executeQuery();
            
            ArrayList<Product> products = new ArrayList<Product>();
            Product p;
            while (result.next()) {
                p = new Product();
                p.setId(result.getInt("id"));
                p.setName(result.getString("name"));
                p.setPrice(result.getDouble("price"));
                p.setDescription(result.getString("description"));
                products.add(p);
            }
            
            return products;
            
        } catch (Exception ex) {
            return null;
        }
    }
    
    public Address getOrderAddress(int id) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_addresses_select(?) }");
            
            statement.setInt(1, id);
            ResultSet result = statement.executeQuery();

            Address address = null;

            if (result.next()) {
                address = new Address(result.getInt("id"), result.getString("address"), result.getString("landmarks"), result.getString("address_title"));
            }

            return address;


        } catch (Exception ex) {
            return null;
        }
    }
    
    public ArrayList getAddress(int id) {
        try {
            this.connect();
            ArrayList<Address> address = new ArrayList<Address>();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_addresses_select_all(?) }");
            
            statement.setInt(1, id);
            ResultSet result = statement.executeQuery();

            Address addre = null;

            while (result.next()) {
                addre = new Address(result.getInt("id"), result.getString("address"), result.getString("landmarks"), result.getString("address_title"));
                address.add(addre);
            }

            return address;


        } catch (Exception ex) {
            return null;
        }
    }
    
    public ArrayList<Product> getProductsWithOutPrices() {
        try {
            
            this.connect();
            
            CallableStatement statement = this.connection.prepareCall("{ CALL sp_products_select_all_wo_prices() }");
            ResultSet result = statement.executeQuery();
            
            ArrayList<Product> products = null;
            products = new ArrayList<Product>();
            Product p;
            while (result.next()) {
                p = new Product();
                p.setId(result.getInt("id"));
                p.setName(result.getString("name"));
                products.add(p);
            }
            
            return products;
            
        } catch (Exception ex) {
            return null;
        }
    }
    
    public ArrayList<OrderLog> getOrderLog(int id) {
        try {
            
            this.connect();
            
            CallableStatement statement = this.connection.prepareCall("{ CALL sp_transactions_log_select( ? ) }");
            statement.setInt(1, id);
            ResultSet result = statement.executeQuery();
            
            ArrayList<OrderLog> logs = new ArrayList<OrderLog>();
            OrderLog log;
            while (result.next()) {
                
                Timestamp date = result.getTimestamp("date");  
                DateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");  
                
                log = new OrderLog();
                log.setStatus(result.getString("status"));
                log.setDate("" + df.format(date));
                logs.add(log);
            }
            
            return logs;
            
        } catch (Exception ex) {
            return null;
        }
    }
    
    public ArrayList<Size> getAvailableSizes(int id) {
        try {
            this.connect();
            CallableStatement statement = this.connection.prepareCall("{ CALL sp_prices_available(?) }");
            statement.setInt(1, id);
            ResultSet result = null;
            result = statement.executeQuery();
            ArrayList<Size> sizes = new ArrayList<Size>();
            Size s;
            while (result.next()) {
               s = new Size();
               s.setId(result.getInt("id"));
               s.setName(result.getString("name"));
               sizes.add(s);
            }
            return sizes;
            
        } catch (Exception ex) {
            return null;
        }
    }
    
    public boolean priceExist(int id, int size) {
        try {
            
            this.connect();
            
            CallableStatement statement = this.connection.prepareCall("{ CALL sp_prices_exist(?, ?) }");
            statement.setInt(1, id);
            statement.setInt(2, size);
            ResultSet result = statement.executeQuery();
            
            return result.next() && result.getInt("total") > 0 ? true : false;
            
        } catch (Exception ex) {
            return false;
        }
    }
    
    public String addPrice(int id, int size, double price) {
        try {
            
            this.connect();
            CallableStatement statement = this.connection.prepareCall("{ CALL sp_prices_insert(?, ?, ?) }");
            statement.setInt(1, id);
            statement.setInt(2, size);
            statement.setDouble(3, price);
            ResultSet result = statement.executeQuery();
            
            return result.next() ? result.getString("name") : "";
            
        } catch (Exception ex) {
            return "";
        }
    }
    
    public boolean removePrice(int id) {
        try {
            
            this.connect();
            CallableStatement statement = this.connection.prepareCall("{ CALL sp_prices_remove( ? ) }");
            statement.setInt(1, id);
            ResultSet result = statement.executeQuery();
            
            return result.next() && result.getInt("total") > 0 ? true : false;
            
        } catch (Exception ex) {
            return false;
        }
    }
    
    public boolean modifyAddress(String addTitle, String address, String landmarks, int id) {
        try {
            this.connect();

            CallableStatement statement = connection.prepareCall("{CALL sp_addresses_update(?, ?, ?, ?)}");
            
            statement.setString(1, addTitle);
            statement.setString(2, address);
            statement.setString(3, landmarks);
            statement.setInt(4, id);

            ResultSet result = statement.executeQuery();

            return (result.next() && (result.getInt("total") != 0)) ? true : false;

        } catch (Exception e) {
            return false;
        }
    }
    
    public boolean removeAddress(int id) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_addresses_delete(?) }");
            statement.setInt(1, id);
            statement.executeUpdate();

            ResultSet result = statement.executeQuery();
            
            return (result.next() && (result.getInt("total") != -1)) ? true : false;
        } catch (Exception ex) {
            return false;
        }
    }
    
     public boolean addOrder(ArrayList<CartItem> items, int id, int add_id) {
        try {
            this.connect();
            double total = 0.0;
            int trans_id = 0;
            
            CallableStatement statement = this.connection.prepareCall("{ CALL sp_transactions_insert(?, ?) }");
            statement.setInt(1, id);
            statement.setInt(2, add_id);
            ResultSet result = statement.executeQuery();
            if(result.next()){
                trans_id = result.getInt("trans_id");
            }
            
            for(CartItem ci : items){
                CallableStatement statementOrder = this.connection.prepareCall("{ CALL sp_orders_insert(?, ?, ?, ?, ?) }");
                statementOrder.setInt(1, ci.getId());
                statementOrder.setInt(2, ci.getQty());
                statementOrder.setDouble(3, ci.total());
                statementOrder.setInt(4, trans_id);
                statementOrder.setInt(5, ci.getSize());
                statementOrder.executeQuery();
                total += ci.total();
            }
            
            double total_amount = total + (total * 0.05);
            CallableStatement statements = this.connection.prepareCall("{ CALL sp_transactions_update_total(?, ?) }");
            statements.setDouble(1, total_amount);
            statements.setInt(2, trans_id);
            ResultSet results = statements.executeQuery();
            
            return (results.next() && (results.getInt("total") != 0)) ? true : false;

        } catch (Exception ex) {
            //return false;
            return false;
        }
    }
     
    public ArrayList getOrders() {
        try {
            this.connect();
            ArrayList<Transaction> transactions = new ArrayList<Transaction>();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_transactions_select_all() }");
            ResultSet result = statement.executeQuery();

            Transaction tempTrans = null;

            while (result.next()) {
                Timestamp date = result.getTimestamp("purchase_date");  
                DateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");  

                tempTrans = new Transaction(result.getInt("transaction_id"), result.getInt("account_id"), result.getInt("address_id"), ("" + df.format(date)), result.getDouble("total_amount"), result.getString("status"));
                
                transactions.add(tempTrans);
            }

            return transactions;

        } catch (Exception ex) {
            return null;
        }

    }

    public Product selectOrder(String id) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_products_select(?) }");
            statement.setString(1, id);
            ResultSet result = statement.executeQuery();

            Product tempProduct = null;

            while (result.next()) {
                tempProduct = new Product(result.getInt("id"), result.getString("name"), result.getInt("category_id"), result.getString("description"), result.getBoolean("active"), result.getString("image"));
            }
            return tempProduct;

        } catch (Exception ex) {
            return null;
        }
    }
    
    public boolean rejectTransaction(int id) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_transactions_reject(?) }");
            statement.setInt(1, id);
            ResultSet result = statement.executeQuery();

            return true;
        } catch (Exception ex) {
            return false;
        }
    }

    public boolean approveTransaction(int id) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_transactions_approve(?) }");
            statement.setInt(1, id);
            ResultSet result = statement.executeQuery();

            return true;
        } catch (Exception ex) {
            return false;
        }
    }
    
    public boolean completeTransaction(int id) {
        try {
            this.connect();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_transactions_complete(?) }");
            statement.setInt(1, id);
            ResultSet result = statement.executeQuery();

            return true;
        } catch (Exception ex) {
            return false;
        }
    }
    
    public ArrayList<Transaction> getUserTransactions(int id) {
        try {
            this.connect();
            ArrayList<Transaction> transactions = new ArrayList<Transaction>();

            CallableStatement statement = this.connection.prepareCall("{ CALL sp_transactions_user( ? ) }");
            statement.setInt(1, id);
            ResultSet result = statement.executeQuery();

            Transaction tempTrans = null;

            while (result.next()) {
                Timestamp date = result.getTimestamp("purchase_date");  
                DateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");  

                tempTrans = new Transaction(result.getInt("transaction_id"), result.getInt("account_id"), result.getInt("address_id"), ("" + df.format(date)), result.getDouble("total_amount"), result.getString("status"));
                
                transactions.add(tempTrans);
            }

            return transactions;
        } catch (Exception ex) {
            return null;
        }
    }  
    
    public Transaction getDetailedHistory(int id) {
        try {
            
            this.connect();
            CallableStatement statement = this.connection.prepareCall("{ CALL sp_transactions_detail( ? ) }");
            statement.setInt(1, id);
            ResultSet result = statement.executeQuery();
            Transaction t = new Transaction();
            
            if (result.next()) {
                
                
                Timestamp date = result.getTimestamp("purchase_date");  
                DateFormat df = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");  
                
                t.setAccount_id(result.getInt("account_id"));
                t.setAddress(result.getInt("address_id"));
                t.setDate_purchased("" + df.format(date));
                t.setStatus(result.getString("status"));
                t.setTotal_amount(result.getDouble("total_amount"));
                t.setTrans_id(result.getInt("transaction_id"));
                
                CallableStatement statement2 = this.connection.prepareCall("{ CALL sp_orders_detail( ? ) }");
                statement2.setInt(1, id);
                ResultSet result2 = statement2.executeQuery();
                
                CartItem ci;
                
                while (result2.next()) {
                    ci = new CartItem();
                    ci.setId(result2.getInt("id"));
                    ci.setName(result2.getString("name"));
                    ci.setPrice(result2.getDouble("price"));
                    ci.setQty(result2.getInt("quantity"));
                    ci.setDescription(result2.getString("description"));
                    
                    t.addItem(ci);
                }
                
                return t;
                
            }
            return null;
            
        } catch (Exception e) {
            return null;
        }
    }
}
